package com.xinchili.mysqlSimulateRedis.util;

import com.xinchili.mysqlSimulateRedis.entity.MysqlCache;

import java.sql.*;

public class JdbcUtils {

    /**
     * 根据key查询缓存
     */
    public static MysqlCache selectCacheByKey(Connection conn, String key) throws SQLException {
        String sql = "SELECT mysql_key, value, expire FROM mysql_cache WHERE mysql_key = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, key);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    MysqlCache cache = new MysqlCache();
                    cache.setMysqlKey(rs.getString("mysql_key"));
                    cache.setValue(rs.getString("value"));
                    cache.setExpire(rs.getLong("expire"));
                    if (rs.wasNull()) {
                        cache.setExpire(null);
                    }
                    return cache;
                }
            }
        }
        return null;
    }

    /**
     * 更新缓存
     */
    public static boolean updateCache(Connection conn, String key, String value, Long expire) throws SQLException {
        String sql = "UPDATE mysql_cache SET value = ?, expire = ? WHERE mysql_key = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, value);
            if (expire != null) {
                ps.setLong(2, expire);
            } else {
                ps.setNull(2, Types.BIGINT);
            }
            ps.setString(3, key);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 插入缓存
     */
    public static void insertCache(Connection conn, String key, String value, Long expire) throws SQLException {
        String sql = "INSERT INTO mysql_cache (mysql_key, value, expire) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, key);
            ps.setString(2, value);
            if (expire != null) {
                ps.setLong(3, expire);
            } else {
                ps.setNull(3, Types.BIGINT);
            }
            ps.executeUpdate();
        }
    }

    /**
     * 根据key删除缓存
     */
    public static void deleteCacheByKey(Connection conn, String key) throws SQLException {
        String sql = "DELETE FROM mysql_cache WHERE mysql_key = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, key);
            ps.executeUpdate();
        }
    }

    /**
     * 建表语句
     */
    public static void initCacheTable(Connection conn) {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("CREATE TABLE IF NOT EXISTS mysql_cache (" +
                    "mysql_key VARCHAR(255) PRIMARY KEY COMMENT '缓存键'," +
                    "value TEXT NOT NULL COMMENT '缓存值'," +
                    "expire BIGINT COMMENT '过期时间戳'" +
                    ")");
        } catch (SQLException e) {
            throw new RuntimeException("初始化缓存表失败", e);
        }
    }
}
